﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Web;
using System.Configuration;

using System.Data;
using MySql.Data.MySqlClient;
using System.Data.Common;


namespace CRMFramework
{
    public abstract class DataAccessObject<T> where T: new()
    {

        #region CRUD

        public void EntityInsert(T entity)
        {
            //StoreProcedure name 
            string sqlSP = "SP_" + entity.GetType().Name + "s_Insert";
            DbParameter[] paramTemp = GetSPParamFromEntiyAllColums(entity);
            //execute Update sql
            DBhelper.ExecuteNonQuerySP(sqlSP, paramTemp);
        }

        public void EntitySave(T entity)
        {
            //StoreProcedure name 
            string sqlSP = "SP_" + entity.GetType().Name + "s_Save";
            DbParameter[] paramTemp = GetSPParamFromEntiyAllColums(entity);
            //execute save sql
            DBhelper.ExecuteNonQuerySP(sqlSP, paramTemp);
        }

        public void EntityUpdate(T entity)
        {
            string sqlSP = "SP_" + entity.GetType().Name + "s_Update";
            DbParameter[] paramTemp = GetSPParamFromEntiyAllColums(entity);
            DBhelper.ExecuteNonQuerySP(sqlSP, paramTemp);
        }

        public DataTable EntitySelectAll()
        {
            string sqlSP = "SP_" + typeof(T).Name + "s_SelectAll";
            return DBhelper.ExecuteDatasetSP(sqlSP).Tables[0];
        }

        public void EntityDeleteByID(object idvalue)
        {
            string sqlSP = "SP_" + typeof(T).Name + "s_DeleteByID";
            DbParameter[] paramTemp = GetSPParamFromEntiyKeycolums(idvalue);
            DBhelper.ExecuteNonQuerySP(sqlSP, paramTemp);
        }

        public DataTable EntitySelectByID(object idvalue)
        {
            string sqlSP = "SP_" + typeof(T).Name + "s_SelectByID";
            DbParameter[] paramTemp = GetSPParamFromEntiyKeycolums(idvalue);
            return DBhelper.ExecuteDatasetSP(sqlSP, paramTemp).Tables[0];
        }


        #endregion

        //insert and update procedure get param = all colums
        public DbParameter[] GetSPParamFromEntiyAllColums(T entity)
        {
            //Reflection get all property = database table colums
            PropertyInfo[] propertyInfos = entity.GetType().GetProperties();

            //StoreProcedure parameter
            List<DbParameter> listParam = new List<DbParameter>();

            for (int i = 0; i < propertyInfos.Length; i++)
            {
                //get property attribute
                var attr = Attribute.GetCustomAttribute(propertyInfos[i], typeof(EntityColumnAttribute));

                //get property value
                object objParamterValue = propertyInfos[i].GetValue(entity, null);
                if (objParamterValue == null || objParamterValue.ToString().Length == 0)
                    continue;

                //if porperty is column for data table, add this property to parameter list           
                if (attr != null)
                {
                    DbParameter tempParam = DBhelper.SetParameter("@" + propertyInfos[i].Name, objParamterValue);
                    listParam.Add(tempParam);
                }
            }

            //convert list<DbParameter>  to array- DbParameter
            DbParameter[] parmSP = new DbParameter[listParam.Count];
            for (int j = 0; j < parmSP.Length; j++)
            {
                parmSP[j] = listParam[j];
            }
            return parmSP;
        }

        //delete and  select procedure get param = id(Iskey=true)
        public DbParameter[] GetSPParamFromEntiyKeycolums(object idvalue)
        {
            T entity = new T();
            //Reflection get all property = database table colums
            PropertyInfo[] propertyInfos = entity.GetType().GetProperties();

            //StoreProcedure parameter
            List<DbParameter> listParam = new List<DbParameter>();

            for (int i = 0; i < propertyInfos.Length; i++)
            {
                //get attribute name
                var attr =
                       (EntityColumnAttribute)Attribute.GetCustomAttribute(propertyInfos[i], typeof(EntityColumnAttribute));

                if (idvalue == null)
                    continue;

                //from entity column attribute to get sp parameter
                if (attr != null && attr.IsKey == true)
                {
                    DbParameter tempParam = DBhelper.SetParameter("@" + propertyInfos[i].Name, idvalue);
                    listParam.Add(tempParam);
                }
                //get Parameter value
            }

            //convert list<DbParameter>  to array- DbParameter
            DbParameter[] parmSP = new DbParameter[listParam.Count];
            for (int j = 0; j < parmSP.Length; j++)
            {
                parmSP[j] = listParam[j];
            }
            return parmSP;
        }

        //Convert a datatable to List<object>
        public List<T> ConvertDataTableToList(DataTable dataTable)
        {
            var list = new List<T>();
            T newT = new T();
            PropertyInfo[] propertyInfos = newT.GetType().GetProperties();
            foreach (DataRow dr in dataTable.Rows)
            {
                newT = new T();
                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    string strName = propertyInfos[i].Name;
                    if (dataTable.Columns.Contains(strName) && dr[strName] != DBNull.Value)
                    { propertyInfos[i].SetValue(newT, dr[strName], null); }
                }
                list.Add(newT);
            }
            return list;
        }

    }
}
